using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Smo = Microsoft.SqlServer.Management.Smo;

using SqlWebAdmin.CSharp;

public partial class Administration_DataEntry : System.Web.UI.Page
{
    private Smo.Table table = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        string database = Page.Request.QueryString.Get(SqlWebAdmin.CSharp.QueryStringKeys.Database);
        if( String.IsNullOrEmpty(database))
            Response.Redirect(SiteUrls.Error(Globals.databaseNotFoundErrorNumber));

        if( String.IsNullOrEmpty(Page.Request.QueryString.Get(SqlWebAdmin.CSharp.QueryStringKeys.Table)))
            Response.Redirect(SiteUrls.Error(Globals.tableNotFoundErrorNumber));

        table = SmoHelper.GetTable();
        if( table != null )
        {
            dataEntryDataSource.ConnectionString = SmoHelper.GetServer().ConnectionContext.ConnectionString + String.Format(";database={0}", database);
            insertOnlyDataSource.ConnectionString = dataEntryDataSource.ConnectionString;

            dataEntryDataSource.SelectCommand = String.Format("select * from {0}.{1}", table.Schema, table.Name);
            insertOnlyDataSource.SelectCommand = dataEntryDataSource.SelectCommand;

            bool hasPrimaryKey = false;
            foreach (Smo.Index index in table.Indexes)
            {
                if (index.IndexKeyType == Microsoft.SqlServer.Management.Smo.IndexKeyType.DriPrimaryKey)
                {
                    hasPrimaryKey = true;
                    break;
                }
            }

            dataGrid.Caption = table.Name;

            if (hasPrimaryKey)
            {
                System.Text.StringBuilder update = new System.Text.StringBuilder();
                System.Text.StringBuilder where = new System.Text.StringBuilder();
                System.Text.StringBuilder insert = new System.Text.StringBuilder();
                System.Text.StringBuilder columns = new System.Text.StringBuilder();

                update.AppendFormat("update [{0}].[{1}] set ", table.Schema, table.Name);
                insert.AppendFormat("INSERT INTO [{0}].[{1}] (", table.Schema, table.Name);

                where.Append(" where ");

                string and = String.Empty;
                string sep = String.Empty;
                foreach (Smo.Column c in table.Columns)
                {
                    update.AppendFormat("{0}[{1}] = @{2}", sep, c.Name, c.Name.Replace(' ', '_'));
                    insert.AppendFormat("{0}[{1}]", sep, c.Name);
                    columns.AppendFormat("{0}@{1}", sep, c.Name.Replace(' ', '_'));

                    if( sep.Length == 0)
                        sep = ",";

                    if (c.InPrimaryKey)
                    {
                        where.AppendFormat(" {0} [{1}] = {2}", and, c.Name, c.Name.Replace(' ', '_'));
                        if( and.Length == 0)
                            and = "AND";
                    }
                }

                update.Append(where.ToString());
                dataEntryDataSource.UpdateCommand = update.ToString();

                insert.AppendFormat(") VALUES ({0})", columns.ToString());
                insertOnlyDataSource.InsertCommand = insert.ToString();

                dataEntryDataSource.DeleteCommand = String.Format("delete from {0}.{1} {2}", table.Schema, table.Name, where.ToString());
            }
//            BindData(String.Empty);
        }
    }

    private void BindData(string orderByColumn)
    {
        Smo.Database db = SmoHelper.GetDatabase();
        if (db != null)
        {
            Smo.Table table = SmoHelper.GetTable();
            if (table != null)
            {
                string orderByExpression = String.Empty;
                foreach (Smo.Column column in table.Columns)
                {
                    if (String.IsNullOrEmpty(orderByExpression) && column.InPrimaryKey)
                        orderByExpression = column.Name;

                    //BoundColumn bc = new BoundColumn();
                    //bc.DataField = column.Name;
                    //bc.HeaderText = column.Name;
                    //bc.SortExpression = column.Name;
                    //dataGrid.Columns.Add(bc);

                    BoundField bf = new BoundField();
                    bf.DataField = column.Name;
                    bf.HeaderText = column.Name;
                    bf.SortExpression = column.Name;

                    //gridView.Columns.Add(bf);
                }

                if (String.IsNullOrEmpty(orderByExpression))
                    orderByExpression = "1"; // sql server allows us to order by ordinals as well

                DataSet ds = db.ExecuteWithResults(String.Format("select * from {0}.{1} order by {2}", table.Schema, table.Name, orderByExpression));
                if (ds != null)
                {
                    this.dataGrid.DataSource = ds;
                    this.dataGrid.DataBind();
                }
            }
        }
    }

    private void dataGrid_Sort(object source, DataGridSortCommandEventArgs e)
    {
        BindData(e.SortExpression);
    }


}
